from ftplib import FTP
import re
from unrar import rarfile
import shutil # 高级文件操作模块
import os,subprocess  # 系统调用模块，子程序模块
import paramiko # ssh
import pymysql  # 连接后台数据库模块
import time
import datetime


def docker_download(g_product,output,g_version):
    '''
    下载集成压缩包方法
    :param g_product: 所属产品名称
    :param output: 修改单输出程序
    :param g_version: 集成包版本
    :return:
    '''
    #username = 'secu-test'
    #password = 'secu-test@test'
    username = 'UF20-test'
    password = 't5w$kDrP'
    ip = '192.168.102.242'
    port = 5212
    ftp = FTP() # 设置一个FTP变量
    ftp.set_debuglevel(2) # 打开调试级别2显示详细信息
    ftp.connect(ip,port) # 连接ftp，server，和端口
    ftp.login(username,password) # 连接的用户名和密码。
    remark1 = '连接ftp成功'
    to_path = '/home/dar/download/' # 文件目录
    to_path1 = '/home/dar/download2/'
    #path1 = '/{}'.format(g_product)
    #path2 = '/test{}'.format(output)
    #path=path1+path2
    #ftp路径变更，修改路径先截位
    #path3 = re.findall('/M[0-9]+.*', output)
    path3 = output # 修改单输出程序
    m_version = g_version # 修改单版本。

    #执行cwd命令，进入目录
    ftp.cwd(path3.encode("UTF-8").decode("latin-1")) # 先编码再解码
    bufsize = 1024  # 最大缓冲区大小
    #获取文件列表
    ftp_list = ftp.nlst() # 获取目录下的文件
    #正则匹配，找到对应集成包
    for filename in ftp_list:
        re_name = re.match('集成-M[0-9]+.*-{}'.format(m_version),filename.encode("latin-1").decode("UTF-8"))
        if re_name != None:
            print(filename.encode("latin-1").decode("UTF-8"))
            file_handle = open(to_path+filename.encode("latin-1").decode("UTF-8"),"wb").write
            ftp.retrbinary("RETR " + filename, file_handle, bufsize)  # 接收服务器文件并写入文件
            file_handle1 = open(to_path1+filename.encode("latin-1").decode('UTF-8'),"wb").write
            ftp.retrbinary("RETR " + filename, file_handle1, bufsize)
            remark2 = '下载{}成功'.format(filename.encode("latin-1").decode("UTF-8"))
            break
    ftp.set_debuglevel(0)  # 关闭调试模式
    ftp.quit() # 退出ftp
    return to_path+filename.encode("latin-1").decode("UTF-8")


def rar_file(path): # 解压到不同的文件夹
    '''
    压缩包解压方法
    :param path: 解压文件的绝对路径
    :return:
    '''
    #解压路径赋值
    to_url = '/home/dar/download/'
    file = rarfile.RarFile(path)  # 需要解压的文件。
    #获取解压列表
    rar_list = file.namelist() # 获取解压文件的列表。
    #根据文件名后缀进行循环正则匹配，分配到不同文件夹中
    for simple_file in rar_list:
        # 根据不同的文件名后缀解压到不同的文件夹中。
        if simple_file in ['Report.dll','Default.xml','UPDSetup.ini']:
            file.extract(simple_file, to_url + 'un_client')
        elif simple_file in ['index.html', 'HSRCP.xml', 'deliver.ini','DELIVERX.INI','I18N.ini','OneQueryConfig.ini','AllQueryConfig.ini','CommonQueryConfig.ini','USERVOUC.INI','ScanConfig.ini']:
            file.extract(simple_file, to_url + 'un_trade')
        elif simple_file in ['Report.ini'] or re.match('.+.fr3',simple_file):
            file.extract(simple_file, to_url + 'report')
        elif re.match('app64', simple_file) != None:
            file.extract(simple_file, to_url + 'app')
        elif re.match('.+\.so', simple_file) != None:
            file.extract(simple_file, to_url + 'so')
        elif re.match('.+\.ini', simple_file) != None:
            file.extract(simple_file, to_url + 'ini')
        elif re.match('.+\.dll', simple_file) != None:
            file.extract(simple_file, to_url + 'dll')
        elif re.match('.+\.sql', simple_file) != None:
            file.extract(simple_file, to_url + 'sql')
        elif re.match('.+\.cll', simple_file) != None:
            file.extract(simple_file, to_url + 'cll')
        elif (re.match('.+\.xls', simple_file) != None) or (re.match('.+.xlsx', simple_file) != None):
            file.extract(simple_file, to_url + 'xls')
        elif re.match('[\u4e00-\u9fa5].+\.xml', simple_file) != None:
            file.extract(simple_file, to_url + 'zn_xml')
        elif (re.match('[[a-z].+\.xml', simple_file) != None) or (re.match('[[A-Z].+.xml', simple_file) != None):
            file.extract(simple_file, to_url + 'us_xml')
        elif re.match('.+\.bpl', simple_file) != None:
            file.extract(simple_file, to_url + 'bpl')


def clear_dir():  # 清空文件夹，
    '''
    清空文件夹方法
    :return:
    '''
    #判断是否存在文件夹
    result = os.path.exists('/home/dar/download/') # 判断是否存在文件夹
    if result == True:
        #存在则删除，之后创建文件夹
        shutil.rmtree('/home/dar/download/')
        # 递归删除文件
        os.makedirs('/home/dar/download/')
        # 生成多层递归目录
    else:
        os.makedirs('/home/dar/download/')

def docker_upload(ip,port,user,password,u_version):
    '''
    将对应文件上传至对应服务器方法
    :param ip: 服务器ip
    :param port: 服务器端口
    :param user: 服务器用户名
    :param password: 服务器密码
    :return:
    '''
    # paramiko 用于连接远程服务器并执行命令。
    #实例化SSH对象,搭建120的ssh连接
    ssh = paramiko.SSHClient()
    # 允许连接不在know_hosts文件中的主机
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    # 连接服务器
    ssh.connect(hostname='192.168.70.120', port=22, username='root', password='centos7@test')

    #搭建SFTP连接
    t = paramiko.Transport(ip, port)  # 建立sftp连接对象。
    t.connect(username=user, password=password)
    # 连接sftp
    sftp = paramiko.SFTPClient.from_transport(t)
    #获取对应文件路径的文件夹目录
    dir_lists = os.listdir('/home/dar/download/')
    # 遍历文件夹
    for dir_list in dir_lists:
        #根据文件夹名称，对每个文件夹里的内容进行区分处理
        if dir_list == 'so':
            sodir_lists = os.listdir('/home/dar/download/so')
            for so in sodir_lists:
                print(u_version)
                #如果版本为V1，则获取容器中的so版本与本地so版本进行比较，如果本地小于容器中版本，则不将so传到对应服务器
              #  if u_version == 'V1':
                # 20200426 zhukd28450 增加对SO v2及以上版本判断的支持,版本格式为V+数字 or v+数字
                if re.match(r'V[1-9]+', u_version, 0):
                    try:
                        try:
                            stdin, stdout, stderr = ssh.exec_command('docker exec CS5_MSG_CENTER1 strings /home/hundsun/appcom/{} |grep V8'.format(so))
                            so_docker_version = int(stdout.read().decode("GBK").split('V8.0.8.')[1].split(' ')[0])
                        except:
                            try:
                                stdin, stdout, stderr = ssh.exec_command('docker exec CS5_MSG_CENTER1 strings /home/hundsun/appcom/{} |grep V8'.format(so))
                                so_docker_version = int(stdout.read().decode("GBK").split('V8.0.9.')[1].split(' ')[0])
                            except:
                                so_docker_version = 0

                        print(so_docker_version)
                        try:
                            so_darout = subprocess.Popen('strings /home/dar/download/so/{} |grep V8'.format(so),
                                                         shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
                            sout, serr = so_darout.communicate()
                            so_upda_version = int(sout.decode("GBK").split('V8.0.8.')[1].split(' ')[0])
                        except:
                            so_darout = subprocess.Popen('strings /home/dar/download/so/{} |grep V8'.format(so),
                                                         shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
                            sout, serr = so_darout.communicate()
                            so_upda_version = int(sout.decode("GBK").split('V8.0.9.')[1].split(' ')[0])
                        print(so_upda_version)

                        # 20200426 zhukd28450 增加对SO v2及以上版本判断的支持，同一修改单可能出现
                        if so_upda_version >= so_docker_version:
                            sftp.put(os.path.join('/home/dar/download/so', so), os.path.join('/home/uf20/hundsun/appcom/', so))
                    except:
                        sftp.put(os.path.join('/home/dar/download/so', so), os.path.join('/home/uf20/hundsun/appcom/', so))
                # 版本输入有误
                else:
                    print("版本信息输入有误")
                    sftp.put(os.path.join('/home/dar/download/so', so),os.path.join('/home/uf20/hundsun/appcom/', so))

        #sql版本判断同上
        elif dir_list == 'sql':
            sqldir_lists = os.listdir('/home/dar/download/sql')
            for sql in sqldir_lists:

                if u_version == 'V1':

                    try:
                        stdin, stdout, stderr = ssh.exec_command(
                            'docker exec CS5_MSG_CENTER1 head -30 /home/hundsun/workspace/C/sql/{}'.format(sql))
                        sql_docker_version = int(stdout.read().decode("GBK").split('V8.0.8.')[1].split(' ')[0])
                    except:
                        try:
                            stdin, stdout, stderr = ssh.exec_command(
                                'docker exec CS5_MSG_CENTER1 head -30 /home/hundsun/workspace/C/sql/{}'.format(sql))
                            sql_docker_version = int(stdout.read().decode("GBK").split('V8.0.9.')[1].split(' ')[0])
                        except:
                            sql_docker_version = 0
                    print(sql_docker_version)


                    try:
                        darout = subprocess.Popen('head -30 /home/dar/download/sql/{}'.format(sql), shell=True,
                                                  stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
                        sout, serr = darout.communicate()
                        sql_upda_version = int(sout.decode("GBK").split('V8.0.8.')[1].split(' ')[0])
                    except:
                        darout = subprocess.Popen('head -30 /home/dar/download/sql/{}'.format(sql), shell=True,
                                                  stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
                        sout, serr = darout.communicate()
                        sql_upda_version = int(sout.decode("GBK").split('V8.0.9.')[1].split(' ')[0])

                    print(sql_upda_version)
                    if sql_upda_version > sql_docker_version:
                        sftp.put(os.path.join('/home/dar/download/sql', sql),os.path.join('/home/uf20/hundsun/workspace/C/sql/', sql))
                    else:
                        os.remove('/home/dar/download/sql/{}'.format(sql))
                else:
                    sftp.put(os.path.join('/home/dar/download/sql', sql),os.path.join('/home/uf20/hundsun/workspace/C/sql/', sql))
        elif dir_list=='bpl':
            bpldir_lists = os.listdir('/home/dar/download/bpl')
            for bpl in bpldir_lists:
                sftp.put(os.path.join('/home/dar/download/bpl',bpl), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',bpl))
        elif dir_list=='dll':
            dlldir_lists = os.listdir('/home/dar/download/dll')
            for dll in dlldir_lists:
                 (os.path.join('/home/dar/download/dll',dll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/biz/',dll))
        elif dir_list=='report':
            reportdir_lists = os.listdir('/home/dar/download/report')
            for report in reportdir_lists:
                sftp.put(os.path.join('/home/dar/download/report',report), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Report/',report))
        elif dir_list=='cll':
            clldir_lists = os.listdir('/home/dar/download/cll')
            for cll in clldir_lists:
                sftp.put(os.path.join('/home/dar/download/cll',cll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/CellsTemplate/',cll))
        elif dir_list=='us_xml':
            us_xmldir_lists = os.listdir('/home/dar/download/us_xml')
            for us_xml in us_xmldir_lists:
                sftp.put(os.path.join('/home/dar/download/us_xml',us_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Data/',us_xml))
        elif dir_list=='zn_xml':
            zn_xmldir_lists = os.listdir('/home/dar/download/zn_xml')
            for zn_xml in zn_xmldir_lists:
                sftp.put(os.path.join('/home/dar/download/zn_xml',zn_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/XmlTemplate/',zn_xml))
        elif dir_list=='un_client':
            clidir_lists = os.listdir('/home/dar/download/un_client')
            for cli in clidir_lists:
                sftp.put(os.path.join('/home/dar/download/un_client',cli), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',cli))
        elif dir_list=='un_trade':
            tradir_lists = os.listdir('/home/dar/download/un_trade')
            for tra in tradir_lists:
                sftp.put(os.path.join('/home/dar/download/un_trade',tra), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/',tra))
    sftp.close()

def cq_docker_upload(ip,port,user,password):
    '''
    将对应文件上传至对应服务器方法
    :param ip: 服务器ip
    :param port: 服务器端口
    :param user: 服务器用户名
    :param password: 服务器密码
    :return:
    '''

    #同上方法 ，区别是此方法建立对应服务器的连接，并且不进行版本判断，目的是让对应服务器重启正常
    t = paramiko.Transport(ip, port)
    t.connect(username=user, password=password)
    sftp = paramiko.SFTPClient.from_transport(t)
    dir_lists = os.listdir('/home/dar/download/')
    for dir_list in dir_lists:
        if dir_list == 'so':
            sodir_lists = os.listdir('/home/dar/download/so')
            for so in sodir_lists:
                sftp.put(os.path.join('/home/dar/download/so', so),os.path.join('/home/uf20/hundsun/appcom/', so))
        elif dir_list == 'sql':
            sqldir_lists = os.listdir('/home/dar/download/sql')
            for sql in sqldir_lists:
                sftp.put(os.path.join('/home/dar/download/sql', sql),os.path.join('/home/uf20/hundsun/workspace/C/sql/', sql))
        elif dir_list=='bpl':
            bpldir_lists = os.listdir('/home/dar/download/bpl')
            for bpl in bpldir_lists:
                sftp.put(os.path.join('/home/dar/download/bpl',bpl), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',bpl))
        elif dir_list=='dll':
            dlldir_lists = os.listdir('/home/dar/download/dll')
            for dll in dlldir_lists:
                sftp.put(os.path.join('/home/dar/download/dll',dll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/biz/',dll))
        elif dir_list=='report':
            reportdir_lists = os.listdir('/home/dar/download/report')
            for report in reportdir_lists:
                sftp.put(os.path.join('/home/dar/download/report',report), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Report/',report))
        elif dir_list=='cll':
            clldir_lists = os.listdir('/home/dar/download/cll')
            for cll in clldir_lists:
                sftp.put(os.path.join('/home/dar/download/cll',cll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/CellsTemplate/',cll))
        elif dir_list=='us_xml':
            us_xmldir_lists = os.listdir('/home/dar/download/us_xml')
            for us_xml in us_xmldir_lists:
                sftp.put(os.path.join('/home/dar/download/us_xml',us_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Data/',us_xml))
        elif dir_list=='zn_xml':
            zn_xmldir_lists = os.listdir('/home/dar/download/zn_xml')
            for zn_xml in zn_xmldir_lists:
                sftp.put(os.path.join('/home/dar/download/zn_xml',zn_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/XmlTemplate/',zn_xml))
        elif dir_list=='un_client':
            clidir_lists = os.listdir('/home/dar/download/un_client')
            for cli in clidir_lists:
                sftp.put(os.path.join('/home/dar/download/un_client',cli), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',cli))
        elif dir_list=='un_trade':
            tradir_lists = os.listdir('/home/dar/download/un_trade')
            for tra in tradir_lists:
                sftp.put(os.path.join('/home/dar/download/un_trade',tra), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/',tra))
    sftp.close()

def docker_upload1(ip,port,user,password,path):
    '''
    将对应文件上传至对应服务器方法
    :param ip: 服务器ip
    :param port: 服务器端口
    :param user: 服务器用户名
    :param password: 服务器密码
    :return:
    '''
    #此方法对应上传文件升级模式，与前一方法无太大差别
    t = paramiko.Transport(ip,port)
    t.connect(username=user,password=password)
    sftp = paramiko.SFTPClient.from_transport(t)
    dir_lists = os.listdir('{}'.format(path))
    for dir_list in dir_lists:
        if dir_list=='so':
            sodir_lists = os.listdir(path+'/so')
            for so in sodir_lists:
                sftp.put(os.path.join(path+'/so',so), os.path.join('/home/uf20/hundsun/appcom/',so))
        elif dir_list=='sql':
            sqldir_lists = os.listdir(path+'/sql')
            for sql in sqldir_lists:
                sftp.put(os.path.join(path+'/sql',sql), os.path.join('/home/uf20/hundsun/workspace/C/sql/',sql))
        elif dir_list=='bpl':
            bpldir_lists = os.listdir(path+'/bpl')
            for bpl in bpldir_lists:
                sftp.put(os.path.join(path+'/bpl',bpl), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',bpl))
        elif dir_list=='dll':
            dlldir_lists = os.listdir(path+'/dll')
            for dll in dlldir_lists:
                sftp.put(os.path.join(path+'/dll',dll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/biz/',dll))
        elif dir_list=='report':
            reportdir_lists = os.listdir(path+'/report')
            for report in reportdir_lists:
                sftp.put(os.path.join(path+'/report',report), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Report/',report))
        elif dir_list=='cll':
            clldir_lists = os.listdir(path+'/cll')
            for cll in clldir_lists:
                sftp.put(os.path.join(path+'/cll',cll), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/CellsTemplate/',cll))
        elif dir_list=='us_xml':
            us_xmldir_lists = os.listdir(path+'/us_xml')
            for us_xml in us_xmldir_lists:
                sftp.put(os.path.join(path+'/us_xml',us_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Data/',us_xml))
        elif dir_list=='zn_xml':
            zn_xmldir_lists = os.listdir(path+'/zn_xml')
            for zn_xml in zn_xmldir_lists:
                sftp.put(os.path.join(path+'/zn_xml',zn_xml), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/XmlTemplate/',zn_xml))
        elif dir_list=='un_client':
            clidir_lists = os.listdir(path+'/un_client')
            for cli in clidir_lists:
                sftp.put(os.path.join(path+'/un_client',cli), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/',cli))
        elif dir_list=='un_trade':
            tradir_lists = os.listdir(path+'/un_trade')
            for tra in tradir_lists:
                sftp.put(os.path.join(path+'/un_trade',tra), os.path.join('/home/uf20/hundsun/workspace/updatedir/HsClient/Trade/',tra))
    sftp.close()



def sqlcommand(sqlpath,tnsname,sqlpassword,id):
    #获取入参的sql存放目录，tns配置名称，对应数据库密码
    path = sqlpath
    client_name = tnsname
    client_password = sqlpassword

    #创建并打开Batch.sql文件
    sql_file = '{}/Batch.sql'.format(path)
    print(sql_file)
    s = open(sql_file, 'w')
    #生成批处理sql脚本---Batch.sql  用与后面批量执行升级包的sql文件
    s.write('spool /hundsun/webForHR/app/static/sqllog1/{}.log\n'.format(id))
    lists = os.listdir(path)  # 返回指定目录下的所有文件和目录名
    sql_list = []
    no_list = []
    print(lists)
    for sql in lists:
        if re.match('[a-z]+_.*', sql) != None:
            sql_name = sql.split('_')[0]
            if sql_name not in sql_list:
                sql_list.append(sql_name)
        else:
            if re.match('.+.sql', sql) != None and sql != 'Batch.sql':
                no_list.append(sql)

    print(sql_list)
    print(no_list)

    for count in sql_list:
        s.write('\nconn hs_{}/{}@{}\n'.format(count, client_password, client_name))
        for sql1 in lists:
            if re.match('[a-z]+_.*', sql1) != None:
                if count == sql1.split('_')[0]:
                    s.write('@{}/{}\n'.format(path, sql1))

    if len(no_list)>0:

        s.write('\n--存在无法识别脚本，执行data库\n')

        s.write('\nconn hs_data/{}@{}\n'.format(client_password, client_name))
        for no in no_list:
            s.write('@{}/{}\n'.format(path, no))
    s.write('spool off;')
    s.write('quit;')
    s.close()










def use_sh(ip,port,user,password,m_number,up_way,output,sqlinfo,turnid,version): # 执行服务器的脚本
    '''
    执行服务器脚本方法
    :param ip: 服务器ip
    :param port: 服务器端口
    :param user: 服务器用户名
    :param password: 服务器密码
    :param m_number :路径名字和版本
    :param up_way :升级方式

    :return:
    '''

    #连接本地mysql服务器
    conn = pymysql.connect(host='10.20.27.20', user='root', passwd='Dar960207!@#', db='dar')
    # 使用cursor()方法创建一个游标
    cur = conn.cursor()

    #实例化SSH对象，搭建120服务器连接
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(hostname='192.168.70.120',port=22,username='root',password='centos7@test')
    print("连接到120服务器，并执行cleanup.sh命令",file = log_file)
    #进入home目录，执行cleanup.sh命令
    # 执行所写的命令
    stdin,stdout,stderr = ssh.exec_command('cd /home/ ;./cleanup.sh')
    # 获取命令结果
    result = stdout.read().decode("UTF-8").replace('\n','')
    #打印返回值
    print("cleanup.sh命令的执行结果为:",result,file = log_file)
    remark = ''
    if result == '清理完成' :
        remark = remark+'清理完成<br>'
        if up_way == 1:
            print("以修改单的方式进行升级，",file = log_file)
            docker_upload(ip='192.168.70.120', port=22, user='root', password='centos7@test',u_version = version)
            result3 = os.path.exists('/home/dar/download/so/') # 判断是否有so，和sql文件
            sqlexist = os.path.exists('/home/dar/download/sql/')
            print("判断是否有so和sql文件,so:",result3,"sql:",sqlexist,file = log_file)
            sqlflag = 1
            if result3 == True:
                remark = remark + '存在so文件，进行版本比对<br>'
                i = 0
                print("存在so文件，进行版本比对",file = log_file)
                solists = os.listdir('/home/dar/download/so')
                print("打印出所有的so文件：",solists,file = log_file)
                begin_version = []
                end_version = []
                print("120镜像中so文件的版本分别为", file=log_file)
                for so in solists:
                    stdin, stdout, stderr = ssh.exec_command(
                        'docker exec CS5_MSG_CENTER1 strings /home/hundsun/appcom/{} |grep V8'.format(so))
                    result5 = stdout.read().decode("UTF-8").replace('\n', '')
                    print(so,result5,file = log_file)
                    begin_version.append(result5)
        elif up_way == 2:
            print("以上传文件的方式进行升级",file = log_file)
            docker_upload1(ip='192.168.70.120', port=22, user='root', password='centos7@test',path=output)
            result3 = os.path.exists(output+'/so')
            sqlexist = os.path.exists(output+'/sql')
            print("判断是否有so和sql文件,so:",result3,"sql:",sqlexist,file = log_file)
            sqlflag = 2
            if result3 == True:
                remark = remark + '存在so文件，进行版本比对<br>'
                print("存在so文件，进行版本比对",file = log_file)
                i = 0
                solists = os.listdir(output+'/so')
                print("打印出所有的so文件：",solists,file = log_file)
                begin_version = []
                end_version = []
                print("120镜像中so文件的版本分别为", file=log_file)
                for so in solists:
                    stdin, stdout, stderr = ssh.exec_command(
                        'docker exec CS5_MSG_CENTER1 strings /home/hundsun/appcom/{} |grep V8'.format(so))
                    result5 = stdout.read().decode("UTF-8").replace('\n', '')
                    print(so,result5,file = log_file)
                    begin_version.append(result5)

        stdin, stdout, stderr = ssh.exec_command('cd /home/ ;convmv -f utf-8 -t gbk /home/uf20/hundsun/workspace/updatedir/HsClient/Trade/XmlTemplate -r --notest; convmv -f utf-8 -t gbk /home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Template -r --notest; convmv -f utf-8 -t gbk /home/uf20/hundsun/workspace/updatedir/HsClient/Trade/Data -r --notest;convmv -f utf-8 -t gbk /home/uf20/hundsun/workspace/updatedir/HsClient/Trade/CellsTemplate -r --notest')
        print('换编码')
        time.sleep(5)
        print("在120环境中进行镜像的制作,并打印出返回结果",file = log_file)
        stdin, stdout, stderr = ssh.exec_command('cd /home/ ;./build.sh  {}'.format(m_number))
        result1 = stdout.read().decode("UTF-8").replace('\n', '')
        print(result1,file = log_file)
        remark = remark+'镜像完成<br>'
        print('镜像完成，进行重启',file = log_file)
        sql_18 = 'update updateturn set progress = "70,镜像完成 正在重启" where id = "{}"'.format(turnid)
        num18 = cur.execute(sql_18)
        conn.commit()

        print("连接到{0}服务器,并执行清理命令".format(ip),file = log_file)
        ssh1 = paramiko.SSHClient()
        ssh1.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh1.connect(hostname=ip, port=port, username=user, password=password)
        stdin, stdout, stderr = ssh1.exec_command('cd /home/ ;./cleanup.sh')
        result1 = stdout.read().decode("UTF-8").replace('\n', '')
        print("返回结果如下:",result1,file = log_file)

        if up_way == 1:
            print("将对应文件上传到{0}服务器上".format(ip),file = log_file)
            cq_docker_upload(ip=ip, port=port, user=user, password=password)
        elif up_way == 2:
            print("将对应文件上传到{0}服务器上".format(ip),file = log_file)
            docker_upload1(ip=ip, port=port, user=user, password=password,path=output)

        print('执行重启节点命令',file = log_file)
        stdin, stdout, stderr = ssh1.exec_command('java -jar /home/uf20/hundsun/workspace/autoStartOneNode/autoStartOneNode.jar {0} {1} {2}'.format(ip,user,password))
        result2 = stdout.read().decode("UTF-8")
        print("执行对应重启节命令的返回结果：",result2,file = log_file)
        stdin, stdout, stderr = ssh1.exec_command('cd /home/ ;./startOnePoint CS5_MSG_CENTER1')
        result4 = stdout.read().decode("UTF-8")
        print("重启CS5_MSG_CENTER1返回结果:",result4,file = log_file)
        stdin, stdout, stderr = ssh.exec_command('cd /home/ ;./startOnePoint CS5_MSG_CENTER1')
        result5 = stdout.read().decode("UTF-8")
        print("重启120CS5_MSG_CENTER1返回结果:",result5,file = log_file)
        time.sleep(2)
        if result3 == True:
            print("查看现在{0}服务器中的so版本".format(ip),file = log_file)
            for so in solists:
                stdin, stdout, stderr = ssh1.exec_command('docker exec CS5_MSG_CENTER1 strings /home/hundsun/appcom/{} |grep V8'.format(so))
                result4 = stdout.read().decode("UTF-8").replace('\n', '')
                print(so,result4,file = log_file)
                end_version.append(result4)
                remark = remark + '{0}的版本变化 {1}--->{2}<br>'.format(so, begin_version[i], end_version[i])
                i = i + 1



        if sqlexist == True:
            print("重启完成；进行执行脚本",file = log_file)
            sql_19 = 'update updateturn set progress = "90,重启完成 正在执行脚本" where id = "{}"'.format(turnid)
            num19 = cur.execute(sql_19)
            conn.commit()

            sql_tnsname = sqlinfo.split(',')[0]
            sql_password = sqlinfo.split(',')[1]

            if sqlflag == 1:
                sql_lists = os.listdir('/home/dar/download/sql')
                if len(sql_lists)>0:
                    sqlcommand(sqlpath='/home/dar/download/sql',tnsname=sql_tnsname,sqlpassword = sql_password,id=turnid)
                    os.system('sqlplus hs_user/{}@{} @{}'.format(sql_password,sql_tnsname,'/home/dar/download/sql/Batch.sql'))
                    os.system('sqlplus hs_user/{}@{} @{}'.format(sql_password, sql_tnsname, '/home/dar/download/sql/Batch.sql'))
            elif sqlflag == 2:
                sqlcommand(sqlpath=output+'/sql', tnsname=sql_tnsname, sqlpassword=sql_password,id=turnid)
                os.system('sqlplus hs_user/{}@{} @{}'.format(sql_password, sql_tnsname, output+'/sql/Batch.sql'))
                os.system('sqlplus hs_user/{}@{} @{}'.format(sql_password, sql_tnsname, output + '/sql/Batch.sql'))

    ssh1.close()
    ssh.close()
    return remark

def number(out):
    x = out.split('/')
    y = x[2].split('-')
    z = y[0]
    return z




if __name__ == '__main__':
    while True:
        try:
            conn = pymysql.connect(host='10.20.27.20', user='root', passwd='Dar960207!@#', db='dar') # 连接本地数据库
            cur = conn.cursor() # 获取游标
            sql_1 = 'select id,output,up_version,en_product,up_host,update_way,begin_date,up_persion from updateturn where up_status = 1 order by id asc limit 1'
            # 获取前台需要的一个升级。
            num = cur.execute(sql_1)
            # 进行查询数据库，获取前台写进去的数据，返回值为查询到几条语句
            data = cur.fetchall()
            # 获取查询到所有信息，并以多重列表的形式呈现。
            if num == 1 and data[0][4] != "UFT":
                # 查询到一条需要升级的服务器，
                now = datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
                log_file = open(r"./log/{0}{1}".format(now, data[0][7]), "w", encoding='utf-8')
                # 新建一个日志文件，然后进行日志的书写。
                print("开始升级了", file=log_file)
                m_time = datetime.datetime.now().strftime('%H%M%S') # 获取当前时间
                start = time.time() # 获取时间戳
                sql_2 = 'select server_user,server_password,server_port,databese from product where server_address = "{}"'.format(data[0][4])
                # 从服务器表中获取名字，密码，端口，数据库ff
                num1 = cur.execute(sql_2) # 执行SQL语句
                data1 = cur.fetchall() # 获取全部执行信息
                sqlcc = data1[0][3] # 数据库名字
                print('aaa')
                print("寻找到要升级的服务器",file = log_file)
                if int(data[0][5]) == 1:
                    print('bbb')
                    # 判断是以修改单的方式升级，还是以上传文件的方式升级。
                    print("以修改单的方式进行升级",file = log_file)
                    clear_dir()
                    print("清理文件夹成功",file = log_file)
                    print("进行集成包的下载",file = log_file)
                    down_info = docker_download(g_product=data[0][3],output=data[0][1],g_version=data[0][2])
                    print(down_info,file = log_file)
                    print("下载集成包完成",file = log_file)
                    sql_7 = 'update updateturn set progress = "10,下载完成 正在解压" where id = "{}"'.format( data[0][0])
                    num7 = cur.execute(sql_7)
                    conn.commit()
                    print("下边进性集成包的解压缩",file = log_file)
                    rar_file(down_info)
                    print("解压集成包完成",file = log_file)
                    # 解压路径
                    sql_8 = 'update updateturn set progress = "30,解压完成 正在镜像" where id = "{}"'.format(data[0][0])
                    num8 = cur.execute(sql_8)
                    conn.commit()
                    m_name = "{}-{}-{}".format(number(data[0][1]),data[0][2],m_time)
                    print("集成包去掉文字的名称为:",m_name,file = log_file)
                    print("下边进行文件上传到120，{0}服务器，".format(data[0][4]),file = log_file)
                    get_remark = use_sh(ip=data[0][4],port=data1[0][2],user=data1[0][0],password=data1[0][1],m_number=m_name,up_way=int(data[0][5]),output=None,sqlinfo=sqlcc,turnid=data[0][0],version=data[0][2])
                elif int(data[0][5]) == 2:
                    print('ccc')
                    print("以上传文件的方式进行升级",file = log_file)
                    m_name = 'linshi-{}'.format(m_time)
                    sql_11 = 'update updateturn set progress = "30,正在镜像" where id = "{}"'.format(data[0][0])
                    num8 = cur.execute(sql_11)
                    conn.commit()
                    get_remark = use_sh(ip=data[0][4], port=data1[0][2], user=data1[0][0], password=data1[0][1],m_number=m_name,up_way=int(data[0][5]),output=data[0][1],sqlinfo=sqlcc,turnid=data[0][0],version=data[0][2])

                end = time.time()
                now_time = datetime.datetime.now().strftime('%H:%M:%S')
                sql_3 = 'update updateturn set up_status = 2,remark="{0}",use_time="{1}" where id = "{2}"'.format(get_remark,end-start,data[0][0])
                logpath = "../static/sqllog/{}.log".format(data[0][0])
                sql_4 = 'insert into updatejour(output,read_status,update_time,message,up_persion,log_path)value("{0}",1,"{1}","{2}","{3}","{4}");'.format(data[0][1],now_time,get_remark,data[0][7],logpath)
                sql_10 = 'update updateturn set progress = "100,升级完成" where id = "{}"'.format(data[0][0])

                num10 = cur.execute(sql_10)
                num1 = cur.execute(sql_3)
                num2 = cur.execute(sql_4)
                print('ddd')

                conn.commit()
                log_file.close()
            else:
                print('没有升级')
        except Exception as e:
            print(e)
            sql_6 = 'update updateturn set up_status = 2 where  id = "{}"'.format(data[0][0])
            print(sql_6)
            num5 = cur.execute(sql_6)
            conn.commit()
        cur.close()
        conn.close()
        time.sleep(6)
